﻿using System;
using MySql.Data.MySqlClient;

namespace TransporteAgua
{
    public class Database
    {
        string user, pass, DBName, Host, connString;
        MySqlConnection conexion;
        MySqlCommand sentenciaSQL;
        MySqlDataReader result;

        public Database(String Host, String DBName, String user, String pass)
        {
            this.user = user;
            this.pass = pass;
            this.DBName = DBName;
            this.Host = Host;
            connString = "SERVER=" + Host + ";" +
                     "PORT=3306;" +
                     "DATABASE=" + DBName + ";" +
                     "UID=" + user + ";" +
                     "PASSWORD=" + pass + ";";
            conexion = new MySqlConnection(connString);
            sentenciaSQL = conexion.CreateCommand();
            conexion.Open();
            conexion.Close();
        }

        // Devuelve el numero de elementos de una tabla
        public int contar(string tabla, string filtro)
        {
            try
            {
                sentenciaSQL.CommandText = "SELECT COUNT(id) FROM `" + DBName + "`.`" + tabla + "` " + filtro;
                conexion.Open();
                result = sentenciaSQL.ExecuteReader();
                result.Read();
                return int.Parse(result.GetValue(0).ToString());
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conexion.Close();
            }
        }

        // devuelve una columna de la tabla
        public string[] getColumn(string campo, string tabla, string filtro, string orden, int numero)
        {
            try
            {
                string[] res = new string[numero];
                sentenciaSQL.CommandText = "SELECT " + campo + " FROM `" + DBName + "`.`" + tabla + "` " + filtro + " ORDER BY " + orden;
                conexion.Open();
                result = sentenciaSQL.ExecuteReader();
                for (int i = 0; result.Read(); i++)
                    res[i] = result.GetValue(0).ToString();
                return res;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conexion.Close();
            }
        }

        // devuelve una fila de la tabla
        public string[] getRow(string id, string tabla)
        {
            try
            {
                sentenciaSQL.CommandText = "SELECT * FROM `" + DBName + "`.`" + tabla + "` WHERE id=" + id;
                conexion.Open();
                result = sentenciaSQL.ExecuteReader();
                string[] res = new string[result.FieldCount];
                for (; result.Read(); )
                    for (int j = 0; j < result.FieldCount; j++)
                        res[j] = result.GetValue(j).ToString();
                return res;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conexion.Close();
            }
        }
        // agrega un registro de la base de datos, devuelve su id
        public string agregar(string tabla, string[] campos, string[] valores)
        {
            try
            {
                string temp1 = "", temp2 = "";
                foreach (string a in campos) temp1 += a + ", ";
                foreach (string b in valores) temp2 += "'" + b + "', ";
                sentenciaSQL.CommandText = "INSERT INTO `" + DBName + "`.`" + tabla + "` (" + temp1.Substring(0, temp1.Length - 2) + ") Values (" + temp2.Substring(0, temp2.Length - 2) + ")";
                conexion.Open();
                sentenciaSQL.ExecuteNonQuery();
                conexion.Close();
                return getLastID(tabla);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally {
                conexion.Close();
            }
        }
        // devuelve ultimo id agregado
        private string getLastID(string tabla)
        {
            try
            {
                sentenciaSQL.CommandText = "SELECT id FROM `" + DBName + "`.`" + tabla + "` ORDER BY id DESC";
                conexion.Open();
                result = sentenciaSQL.ExecuteReader();
                result.Read();
                return result.GetValue(0).ToString();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conexion.Close();
            }
        }

        // modifica un registro
        public void modificar(string tabla, string indice, string[] campos, string[] valores)
        {
            try
            {
                string temp = "";
                for (int i = 0; i < campos.Length; i++)
                    temp += campos[i] + "= '" + valores[i] + "', ";
                sentenciaSQL.CommandText = "UPDATE `" + DBName + "`.`" + tabla + "` SET " + temp.Substring(0, temp.Length - 2) + " WHERE id='" + indice + "'";
                conexion.Open();
                sentenciaSQL.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conexion.Close();
            }
        }

        // elimina un registro
        public void eliminar(string tabla, string indice)
        {
            try
            {
                sentenciaSQL.CommandText = "DELETE FROM `" + DBName + "`.`" + tabla + "` WHERE id= '" + indice + "'";
                conexion.Open();
                sentenciaSQL.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conexion.Close();
            }
        }
    }
}
